const db = require("../config/mysql.js");

// 生成日期范围内的所有日期列表 (YYYY-MM-DD)
const getDatesInRange = (startDateStr, endDateStr) => {
  const dates = [];
  let currentDate = new Date(startDateStr);
  const endDate = new Date(endDateStr);

  if (isNaN(currentDate) || isNaN(endDate) || currentDate > endDate) {
    return dates;
  }

  while (currentDate <= endDate) {
    dates.push(currentDate.toISOString().split("T")[0]);
    // 递增
    currentDate.setDate(currentDate.getDate() + 1);
  }
  return dates;
};

class CostStatisticsModel {
  constructor() {
    this.db = db;
    this.tableName_daily = "daily_logs";
    this.tableName_goods = "goods";
    this.tableName_category = "category";
    this.tableName_goods_order = "goods_order";
    this.tableName_goods_order_item = "goods_order_item";
    this.tableName_inventoryout = "inventoryout";
    this.tableName_inventoryout_item = "inventoryoutitem";
    this.tableName_stall = "stall";
  }

  // 根据日期范围，获取用餐人数
  async getDiningCountByDateRange(startDate = null, endDate = null) {
    const queryParams = [];
    const whereConditions = [];

    if (startDate && endDate) {
      whereConditions.push(`log_date BETWEEN ? AND ?`);
      queryParams.push(startDate, endDate);
    } else if (startDate) {
      whereConditions.push(`log_date >= ?`);
      queryParams.push(startDate);
    } else if (endDate) {
      whereConditions.push(`log_date <= ?`);
      queryParams.push(endDate);
    }

    const whereClause =
      whereConditions.length > 0
        ? `WHERE ${whereConditions.join(" AND ")}`
        : "";

    const sql = `SELECT
    SUM(
    IFNULL(meal_count, 0) + 
    IFNULL(qcode_count, 0) + 
    IFNULL(card_count, 0) + 
    IFNULL(ticket_count, 0)
    ) AS totalDiningCount,
     SUM(IFNULL(meal_count, 0)) AS mealDiningCount,
     SUM(IFNULL(qcode_count, 0)) AS qcodeDiningCount,
     SUM(IFNULL(card_count, 0)) AS cardDiningCount,
     SUM(IFNULL(ticket_count, 0)) AS ticketDiningCount
     FROM ${this.tableName_daily}
     ${whereClause}`;

    const [result] = await this.db.query(sql, queryParams);

    if (result.length > 0 && result[0].totalDiningCount !== null) {
      return {
        totalDiningCount: Number(result[0].totalDiningCount) || 0,
        mealDiningCount: Number(result[0].mealDiningCount) || 0,
        qcodeDiningCount: Number(result[0].qcodeDiningCount) || 0,
        cardDiningCount: Number(result[0].cardDiningCount) || 0,
        ticketDiningCount: Number(result[0].ticketDiningCount) || 0,
      };
    }

    return {
      totalDiningCount: 0,
      mealDiningCount: 0,
      qcodeDiningCount: 0,
      cardDiningCount: 0,
      ticketDiningCount: 0,
    };
  }

  // 根据筛选条件，统计各分类的总数量和总金额。出库
  async getCategoryOutboundQuantityStat(
    statusList = [2],
    startDate = null,
    endDate = null,
    purposeList = [1],
    stallIds = null
  ) {
    let effectiveStallIds = [];
    if (typeof stallIds === "string") {
      effectiveStallIds = stallIds.split(",").filter((id) => id !== "");
    } else if (Array.isArray(stallIds)) {
      effectiveStallIds = stallIds.map(String).filter((id) => id !== "");
    } else if (stallIds !== null && stallIds !== undefined) {
      effectiveStallIds = [String(stallIds)];
    }

    const whereConditions = [];
    const queryParams = [];

    if (effectiveStallIds.length > 0) {
      whereConditions.push(
        `io.stall_id IN (${effectiveStallIds.map(() => "?").join(",")})`
      );
      queryParams.push(...effectiveStallIds);
    }

    if (statusList && statusList.length > 0) {
      whereConditions.push(
        `io.status IN (${statusList.map(() => "?").join(",")})`
      );
      queryParams.push(...statusList.map(String));
    }

    if (purposeList && purposeList.length > 0) {
      whereConditions.push(
        `io.purpose IN (${purposeList.map(() => "?").join(",")})`
      );
      queryParams.push(...purposeList.map(String));
    }

    if (startDate && endDate) {
      whereConditions.push(`io.out_time >= ? AND io.out_time <= ?`);
      queryParams.push(startDate + " 00:00:00", endDate + " 23:59:59");
    } else if (startDate) {
      whereConditions.push(`io.out_time >= ?`);
      queryParams.push(startDate + " 00:00:00");
    } else if (endDate) {
      whereConditions.push(`io.out_time <= ?`);
      queryParams.push(endDate + " 23:59:59");
    }

    const whereClause = whereConditions.length
      ? `WHERE ${whereConditions.join(" AND ")}`
      : "";

    const sql = `SELECT 
            c.cate_id AS categoryId,
            c.name AS categoryName,
            c.cate_order AS sortOrder,
            ROUND(IFNULL(SUM(ioi.quantity), 2), 2) AS totalOutboundQuantity,
            ROUND(IFNULL(SUM(ioi.amount), 0), 2) AS totalOutboundAmount
            FROM ${this.tableName_category} c
            LEFT JOIN ${this.tableName_goods} g 
            ON c.cate_id = g.cate_id AND g.status = 1
            LEFT JOIN ${this.tableName_inventoryout_item} ioi 
            ON g.goods_id = ioi.goods_id
            LEFT JOIN ${this.tableName_inventoryout} io 
            ON ioi.out_id = io.out_id 
            LEFT JOIN ${this.tableName_stall} s
            ON io.stall_id = s.stall_id
            ${whereClause}
            GROUP BY c.cate_id, c.name, c.cate_order
            HAVING totalOutboundQuantity > 0 
            ORDER BY c.cate_order ASC, totalOutboundQuantity DESC`;

    const [result] = await this.db.query(sql, queryParams);

    const pieChartData = result
      .map((item) => ({
        name: item.categoryName,
        value: Number(item.totalOutboundQuantity) || 0,
        extend: {
          totalAmount: Number(item.totalOutboundAmount) || 0,
        },
      }))
      .filter((item) => item.value > 0);

    return {
      rawList: result,
      pieChartData: pieChartData,
      filter: {
        statusList,
        purposeList,
        stallIds: effectiveStallIds,
        startDate,
        endDate,
      },
    };
  }

  // 根据筛选条件，分析各档口在各分类上的总出库成本
  async getPurchaseCostAnalysis(
    statusList = [2],
    startDate = null,
    endDate = null,
    purposeList = [1],
    stallIds = null
  ) {
    let effectiveStallIds = [];
    if (typeof stallIds === "string") {
      effectiveStallIds = stallIds.split(",").filter((id) => id !== "");
    } else if (Array.isArray(stallIds)) {
      effectiveStallIds = stallIds.map(String).filter((id) => id !== "");
    } else if (stallIds !== null && stallIds !== undefined) {
      effectiveStallIds = [String(stallIds)];
    }

    const whereConditions = [];
    const queryParams = [];

    if (effectiveStallIds.length > 0) {
      whereConditions.push(
        `io.stall_id IN (${effectiveStallIds.map(() => "?").join(",")})`
      );
      queryParams.push(...effectiveStallIds);
    }

    if (statusList && statusList.length > 0) {
      whereConditions.push(
        `io.status IN (${statusList.map(() => "?").join(",")})`
      );
      queryParams.push(...statusList.map(String));
    }

    if (purposeList && purposeList.length > 0) {
      whereConditions.push(
        `io.purpose IN (${purposeList.map(() => "?").join(",")})`
      );
      queryParams.push(...purposeList.map(String));
    }

    if (startDate && endDate) {
      whereConditions.push(`io.out_time >= ? AND io.out_time <= ?`);
      queryParams.push(startDate + " 00:00:00", endDate + " 23:59:59");
    } else if (startDate) {
      whereConditions.push(`io.out_time >= ?`);
      queryParams.push(startDate + " 00:00:00");
    } else if (endDate) {
      whereConditions.push(`io.out_time <= ?`);
      queryParams.push(endDate + " 23:59:59");
    }

    const whereClause = whereConditions.length
      ? `WHERE ${whereConditions.join(" AND ")}`
      : "";

    const sql = `SELECT 
            c.cate_id AS categoryId,
            c.name AS categoryName,
            c.cate_order AS sortOrder,
            s.stall_id AS stallId,
            s.name AS stallName, 
            g.goods_id AS goodsId,
            g.name AS goodsName,
            ROUND(IFNULL(SUM(ioi.amount), 0), 2) AS goodsTotalCost 
            FROM ${this.tableName_category} c
            LEFT JOIN ${this.tableName_goods} g 
            ON c.cate_id = g.cate_id AND g.status = 1
            LEFT JOIN ${this.tableName_inventoryout_item} ioi 
            ON g.goods_id = ioi.goods_id
            LEFT JOIN ${this.tableName_inventoryout} io 
            ON ioi.out_id = io.out_id 
            LEFT JOIN ${this.tableName_stall} s
            ON io.stall_id = s.stall_id
            ${whereClause}
            GROUP BY c.cate_id, c.name, c.cate_order, s.stall_id, s.name, g.goods_id, g.name
            HAVING goodsTotalCost > 0 
            ORDER BY c.cate_order ASC, s.stall_id ASC, goodsTotalCost DESC`;

    const [result] = await this.db.query(sql, queryParams);

    const stallCostByCate = new Map();
    const allCategories = new Map();
    const goodsCostDetails = new Map();

    result.forEach((item) => {
      const categoryName = item.categoryName;
      const stallName = item.stallName || "未分配档口";
      const goodsTotalCost = Number(item.goodsTotalCost);

      if (!allCategories.has(categoryName)) {
        allCategories.set(categoryName, item.sortOrder);
      }

      if (!stallCostByCate.has(stallName)) {
        stallCostByCate.set(stallName, new Map());
      }
      const stallCateMap = stallCostByCate.get(stallName);
      const currentCategoryTotal =
        (stallCateMap.get(categoryName) || 0) + goodsTotalCost;
      stallCateMap.set(categoryName, currentCategoryTotal);

      if (!goodsCostDetails.has(categoryName)) {
        goodsCostDetails.set(categoryName, []);
      }
      goodsCostDetails.get(categoryName).push({
        goodsName: item.goodsName,
        stallName: stallName,
        cost: goodsTotalCost,
      });
    });

    const sortedCategoryEntries = Array.from(allCategories.entries()).sort(
      (a, b) => {
        return a[1] - b[1];
      }
    );
    const xAxisData = sortedCategoryEntries.map((entry) => entry[0]);

    const series = [];
    for (const [stallName, costMap] of stallCostByCate.entries()) {
      const alignedData = [];
      for (const categoryName of xAxisData) {
        const amount = costMap.get(categoryName) || 0;
        alignedData.push(Number(amount.toFixed(2)));
      }

      series.push({
        name: stallName,
        data: alignedData,
      });
    }

    const costDetails = {};
    for (const [categoryName, goodsList] of goodsCostDetails.entries()) {
      const sortedGoodsList = goodsList.sort((a, b) => b.cost - a.cost);

      costDetails[categoryName] = sortedGoodsList.map((item) => ({
        name: item.goodsName,
        stall: item.stallName,
        cost: Number(item.cost.toFixed(2)),
      }));
    }

    const aggregatedCostMap = new Map();
    result.forEach((item) => {
      const categoryName = item.categoryName;
      const amount = Number(item.goodsTotalCost);

      if (aggregatedCostMap.has(categoryName)) {
        aggregatedCostMap.get(categoryName).totalAmount += amount;
      } else {
        aggregatedCostMap.set(categoryName, {
          categoryName: categoryName,
          categoryId: item.categoryId,
          totalAmount: amount,
          sortOrder: item.sortOrder,
        });
      }
    });

    const pieChartData = Array.from(aggregatedCostMap.values())
      .map((item) => ({
        name: item.categoryName,
        value: Number(item.totalAmount.toFixed(2)),
        extend: {
          categoryId: item.categoryId,
        },
      }))
      .filter((item) => item.value > 0)
      .sort((a, b) => b.value - a.value);

    return {
      rawList: result,
      xAxisData,
      series: series,
      pieChartData: pieChartData,
      costDetails: costDetails,
      filter: {
        statusList,
        purposeList,
        stallIds: effectiveStallIds,
        startDate,
        endDate,
      },
    };
  }

  // 根据筛选条件，统计各档口的总出库成本（金额），并计算其在总成本中的占比。
  async getStallCostRatioStat(
    statusList = [2],
    startDate = null,
    endDate = null,
    purposeList = [1],
    stallIds = null
  ) {
    let effectiveStallIds = [];
    if (typeof stallIds === "string") {
      effectiveStallIds = stallIds.split(",").filter((id) => id !== "");
    } else if (Array.isArray(stallIds)) {
      effectiveStallIds = stallIds.map(String).filter((id) => id !== "");
    } else if (stallIds !== null && stallIds !== undefined) {
      effectiveStallIds = [String(stallIds)];
    }

    const whereConditions = [];
    const queryParams = [];

    if (effectiveStallIds.length > 0) {
      whereConditions.push(
        `s.stall_id IN (${effectiveStallIds.map(() => "?").join(",")})`
      );
      queryParams.push(...effectiveStallIds);
    }

    if (statusList && statusList.length > 0) {
      whereConditions.push(
        `io.status IN (${statusList.map(() => "?").join(",")})`
      );
      queryParams.push(...statusList.map(String));
    }

    if (purposeList && purposeList.length > 0) {
      whereConditions.push(
        `io.purpose IN (${purposeList.map(() => "?").join(",")})`
      );
      queryParams.push(...purposeList.map(String));
    }

    if (startDate && endDate) {
      whereConditions.push(`io.out_time >= ? AND io.out_time <= ?`);
      queryParams.push(startDate + " 00:00:00", endDate + " 23:59:59");
    } else if (startDate) {
      whereConditions.push(`io.out_time >= ?`);
      queryParams.push(startDate + " 00:00:00");
    } else if (endDate) {
      whereConditions.push(`io.out_time <= ?`);
      queryParams.push(endDate + " 23:59:59");
    }

    const whereClause = whereConditions.length
      ? `WHERE ${whereConditions.join(" AND ")}`
      : "";

    const sql = `SELECT 
            s.stall_id AS stallId,
            s.name AS stallName,
            ROUND(IFNULL(SUM(ioi.amount), 0), 2) AS totalAmount 
            FROM ${this.tableName_stall} s
            LEFT JOIN ${this.tableName_inventoryout} io 
            ON s.stall_id = io.stall_id
            LEFT JOIN ${this.tableName_inventoryout_item} ioi 
            ON io.out_id = ioi.out_id 
            ${whereClause}
            GROUP BY s.stall_id, s.name
            HAVING totalAmount > 0 
            ORDER BY totalAmount DESC`;

    const [result] = await this.db.query(sql, queryParams);

    const pieChartData = result
      .map((item) => ({
        name: item.stallName,
        value: Number(item.totalAmount) || 0,
        extend: {
          stallId: item.stallId,
        },
      }))
      .filter((item) => item.value > 0);

    return {
      rawList: result,
      pieChartData: pieChartData,
      filter: {
        statusList,
        purposeList,
        stallIds: effectiveStallIds,
        startDate,
        endDate,
      },
    };
  }
  // 统计各分类下食材的数量 状态为1的
  async getGoodsCountByCategoryStat() {
    const sql = `SELECT
            c.cate_id AS categoryId,
            c.name AS categoryName,
            c.cate_order AS sortOrder,
            COUNT(g.goods_id) AS goodsCount
            FROM ${this.tableName_category} c
            LEFT JOIN ${this.tableName_goods} g
            ON c.cate_id = g.cate_id AND g.status = 1
            GROUP BY c.cate_id, c.name, c.cate_order
            HAVING goodsCount > 0
            ORDER BY c.cate_order ASC, goodsCount DESC`;

    const [result] = await this.db.query(sql);

    const pieChartData = result
      .map((item) => ({
        name: item.categoryName,
        value: Number(item.goodsCount) || 0,
        extend: {
          categoryId: item.categoryId,
        },
      }))
      .filter((item) => item.value > 0);

    return {
      rawList: result,
      pieChartData: pieChartData,
      totalGoodsCount: pieChartData.reduce((sum, item) => sum + item.value, 0),
    };
  }

  // 每日的总成本、档口成本
  async getDailyTotalCostStat(
    statusList = [2],
    startDate = null,
    endDate = null,
    purposeList = [1],
    stallIds = null
  ) {
    let effectiveStallIds = [];
    if (typeof stallIds === "string") {
      effectiveStallIds = stallIds.split(",").filter((id) => id !== "");
    } else if (Array.isArray(stallIds)) {
      effectiveStallIds = stallIds.map(String).filter((id) => id !== "");
    } else if (stallIds !== null && stallIds !== undefined) {
      effectiveStallIds = [String(stallIds)];
    }

    const whereConditions = [];
    const queryParams = [];

    if (effectiveStallIds.length > 0) {
      whereConditions.push(
        `io.stall_id IN (${effectiveStallIds.map(() => "?").join(",")})`
      );
      queryParams.push(...effectiveStallIds);
    }

    if (statusList && statusList.length > 0) {
      whereConditions.push(
        `io.status IN (${statusList.map(() => "?").join(",")})`
      );
      queryParams.push(...statusList.map(String));
    }

    if (purposeList && purposeList.length > 0) {
      whereConditions.push(
        `io.purpose IN (${purposeList.map(() => "?").join(",")})`
      );
      queryParams.push(...purposeList.map(String));
    }

    // 日期范围
    if (startDate && endDate) {
      whereConditions.push(`io.out_time >= ? AND io.out_time <= ?`);
      queryParams.push(startDate + " 00:00:00", endDate + " 23:59:59");
    } else {
      // 必须要有日期范围才能生成折线图
      return { rawList: [], dailyData: { dates: [], series: [] }, filter: {} };
    }

    const whereClause = whereConditions.length
      ? `WHERE ${whereConditions.join(" AND ")}`
      : "";

    const sql = `SELECT 
            DATE(io.out_time) AS dailyDate,
            io.stall_id AS stallId,
            s.name AS stallName,
            ROUND(IFNULL(SUM(ioi.amount), 0), 2) AS totalDailyCost
        FROM ${this.tableName_inventoryout} io 
        INNER JOIN ${this.tableName_inventoryout_item} ioi 
            ON io.out_id = ioi.out_id 
        LEFT JOIN ${this.tableName_stall} s 
            ON io.stall_id = s.stall_id
        ${whereClause}
        GROUP BY dailyDate, stallId, stallName
        HAVING totalDailyCost >= 0 
        ORDER BY dailyDate ASC, stallId ASC`;

    const [result] = await this.db.query(sql, queryParams);

    const allDates = getDatesInRange(startDate, endDate);
    const dailyCostByStall = new Map();

    result.forEach((item) => {
      const dateKey =
        item.dailyDate instanceof Date
          ? item.dailyDate.toISOString().split("T")[0]
          : String(item.dailyDate);
      const stallId = String(item.stallId || "0");
      const stallName = item.stallName || "未分配档口";
      const cost = Number(item.totalDailyCost);

      if (!dailyCostByStall.has(stallId)) {
        dailyCostByStall.set(stallId, {
          name: stallName,
          costs: new Map(),
        });
      }
      dailyCostByStall.get(stallId).costs.set(dateKey, cost);
    });

    const series = [];
    const totalDailyCosts = new Array(allDates.length).fill(0);

    // 档口系列数据
    for (const [stallId, stallData] of dailyCostByStall.entries()) {
      const stallSeriesData = [];

      allDates.forEach((date, index) => {
        const cost = stallData.costs.get(date) || 0;
        stallSeriesData.push(cost);
        // 累加计算
        totalDailyCosts[index] += cost;
      });

      series.push({
        name: stallData.name,
        data: stallSeriesData.map((c) => Number(c.toFixed(2))),
        type: "line",
        extend: { isTotal: false, stallId: stallId },
      });
    }

    series.unshift({
      name: "食材总成本",
      data: totalDailyCosts.map((c) => Number(c.toFixed(2))),
      type: "line",
      lineStyle: { width: 3, type: "solid" },
      itemStyle: { color: "#409EFF" },
      extend: { isTotal: true },
    });

    return {
      rawList: result,
      dailyData: {
        dates: allDates,
        series: series,
      },
      filter: {
        statusList,
        purposeList,
        stallIds: effectiveStallIds,
        startDate,
        endDate,
      },
    };
  }
}

module.exports = CostStatisticsModel;
